<?php

namespace App;

use Illuminate\Notifications\Notifiable;
use Illuminate\Foundation\Auth\User as Authenticatable;
use DB;


class Coupon extends Authenticatable
{
    use Notifiable;
	protected $table = 'coupon';

    /**
     * The attributes that should be hidden for arrays.
     *
     * @var array
     */
    protected $hidden = [
         'remember_token',
    ];

    // 优惠券列表
    public function getCouponsList(){
	    $coupons = DB::table('coupon')
		    ->leftjoin('user_coupon', 'coupon.id', '=', 'user_coupon.coupon_id')
		    ->select('coupons.*')
		    ->get()
		    ->toArray();
	    if(!$coupons){
		    return array();
	    }
	    return $coupons;
    }


	// 根据id或者名称
	public static function getNameById($id){
		$name = '';
		$name = Coupon::where('id',$id)->pluck('name');
		if($name){
			$name = $name[0];
		}
		return $name;
	}





	// 每个用户的优惠券情况统计
    public static function getCouponEveryUserTotal($phone){
		$sql = " SELECT su.* FROM
		(	
	SELECT nu.phone,nu.nickname,nu.notUsedTotal,u.usedTotal,ep.expireTotal FROM (
	   SELECT b.phone AS phone,b.nickname as nickname,COUNT(coupon_id) AS notUsedTotal  FROM c_user_coupon AS a LEFT JOIN  c_user_account AS b ON a.open_id=b.open_id	WHERE a.useStatus=1 GROUP BY phone,nickname 
	) AS nu
	LEFT JOIN (
	  SELECT b.phone AS phone,COUNT(coupon_id) AS usedTotal  FROM c_user_coupon AS a LEFT JOIN  c_user_account AS b ON a.open_id=b.open_id	WHERE a.useStatus=2 GROUP BY phone 
	) AS u ON nu.phone = u.phone 
	LEFT JOIN (
	  SELECT b.phone AS phone,COUNT(coupon_id) AS expireTotal  FROM c_user_coupon AS a LEFT JOIN  c_user_account AS b ON a.open_id=b.open_id	WHERE a.useStatus=3 GROUP BY phone
	) AS ep ON nu.phone=ep.phone	
		) AS su WHERE 1=1 ";

	    if($phone){
		    $sql.=" AND phone like '%$phone%'";
	    }

	    $coupons = DB::select($sql);
	    if(!$coupons){
		    return array();
	    }
	    return $coupons;

    }


}
